﻿using Microsoft.Data.Sqlite;
using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;

namespace SSC.Services
{
    /// <summary>
    /// SqlLite数据库服务的实现
    /// </summary>
    public class SQLiteDatabaseService : IDatabaseService
    {
        private string _connectionString;

        // 构造函数，传入数据库连接字符串
        public SQLiteDatabaseService(string connectionString)
        {
            _connectionString = connectionString;
        }

        // 获取所有数据的方法
        public List<T> GetAll<T>(string query, params SqliteParameter[] parameters) where T : new()
        {
            List<T> results = new List<T>();

            using (var connection = new SqliteConnection(_connectionString))
            {
                connection.Open();
                using var command = new SqliteCommand(query, connection);
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }

                using var reader = command.ExecuteReader();
                var properties = typeof(T).GetProperties()
                    .ToDictionary(p => p.GetCustomAttribute<ColumnAttribute>()?.Name ?? p.Name, p => p);

                while (reader.Read())
                {
                    var obj = new T();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string columnName = reader.GetName(i);

                        if (properties.TryGetValue(columnName, out var property) && reader[i] != DBNull.Value)
                        {
                            property.SetValue(obj, Convert.ChangeType(reader[i], property.PropertyType));
                        }
                    }

                    results.Add(obj);
                }
            }

            return results;
        }

        /// <summary>
        /// 执行 SQL 命令（支持手动指定参数）
        /// </summary>
        /// <param name="query">SQL 语句</param>
        /// <param name="parameters">可选的 SQL 参数数组</param>
        /// <returns>执行结果</returns>
        public bool ExecuteCommand(string query, params SqliteParameter[] parameters)
        {
            using (var connection = new SqliteConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SqliteCommand(query, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    int result = command.ExecuteNonQuery();
                    return result > 0; // 返回是否成功执行
                }
            }
        }
        public async Task<bool> ExecuteCommandAsync(string query, params SqliteParameter[] parameters)
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();
            using var command = new SqliteCommand(query, connection);

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            int result = await command.ExecuteNonQueryAsync();
            return result > 0;
        }
        /// <summary>
        ///  执行 SQL 命令（自动将对象属性映射为 SQL 参数）。
        /// </summary>
        /// <param name="query">SQL 语句，其中参数应以 @PropertyName 形式命名</param>
        /// <param name="parameters">具有属性的对象或实体，方法会自动将对象的属性名称映射为 SQL 参数</param>
        /// <returns>执行结果</returns>
        public bool Execute(string query, object parameters)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();
            using var command = new SqliteCommand(query, connection);
            foreach (var property in parameters.GetType().GetProperties())
            {
                var columnAttribute = property.GetCustomAttributes(typeof(ColumnAttribute), false).FirstOrDefault() as ColumnAttribute;
                var parameterName = columnAttribute != null ? columnAttribute.Name : property.Name;
                var value = property.GetValue(parameters) ?? DBNull.Value;
                command.Parameters.AddWithValue($"@{parameterName}", value);
            }

            int result = command.ExecuteNonQuery();
            return result > 0; // 返回是否成功执行
        }

        /// <summary>
        /// 执行 SQL 查询并返回单个值
        /// </summary>
        /// <typeparam name="T">返回值的类型</typeparam>
        /// <param name="query">SQL 语句</param>
        /// <param name="parameters">可选的 SQL 参数数组</param>
        /// <returns>查询结果</returns>
        public T ExecuteScalar<T>(string query, params SqliteParameter[] parameters)
        {
            using (var connection = new SqliteConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SqliteCommand(query, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    object result = command.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        return (T)Convert.ChangeType(result, typeof(T));
                    }
                    return default;
                }
            }
        }
       
        /// <summary>
        /// 执行 SQL 查询并返回单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="mapFunc"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T ExecuteObject<T>(string query, Func<SqliteDataReader, T> mapFunc, params SqliteParameter[] parameters)
        {
            using (var connection = new SqliteConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SqliteCommand(query, connection))
                {
                    if (parameters != null)
                        command.Parameters.AddRange(parameters);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return mapFunc(reader);
                        }
                    }
                }
            }
            return default;
        }

    }
}
